package init;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;

import oracle.jdbc.OracleResultSet;
import oracle.sql.BLOB;

//import oracle.sql.BLOB;


public class DBManagement extends Database {
	public DBManagement(){
		
	}
	/** The result set of the db query represented in a 2 dimensional array. */
	protected String[][] m_twoDArray = null;
	/** The number or rows in the result rowset. */
	protected int m_numRows = 0;
    /** The number or columns in the result rowset. */
    protected int m_numCols = 0;
    /** The result set's column names. */
    protected String[] m_colNames = null;
    /** The maximum number of rows that the result set will contain. */
    protected int m_maxRows = 5000000;
    /**
     * Move the current pointer to the next row in the result set.
     *
     *
     * @param rset
     * @return true if the pointer is moved successfully.
     * @return false if there are no more rows in the result set.
     */
    private boolean nextResSetRow( ResultSet rset ){
       boolean ret = false;

       if( rset != null )
       {
          try
          {
             ret = rset.next();
          }
          catch( Exception e )
          {
             // nothing on purpose
          }
       }

       return ( ret );
    }
    
    /**
     * Return the number of columns in the query's result data.
     *
     * @return The number of columns.
     */
    public int getNumCols()
    {
       return ( m_numCols );
    }
    
    public int getNumRows()
    {
       return ( m_numRows );
    }

    /**
     * Assign the column names from the result set.
     *
     * @param rsetMetaData
     */
    private void assignColNames( ResultSetMetaData rsetMetaData )
    {
       if( rsetMetaData != null )
       {
          int numCols = getNumCols();

          m_colNames = new String[ numCols ];

          for( int colNum = 0; colNum < numCols; colNum++ )
          {
             try
             {
                m_colNames[ colNum ] = rsetMetaData.getColumnName( colNum + 1 );
             }
             catch( Exception sqle )
             {
                sqle.printStackTrace();

                m_colNames[ colNum ] = "";
             }
          }
       }
    }
    
    /**
     * Get the item for specified column in the current row of the rowset
     * of data.
     *
     *
     * @param rset
     * @param col The column to get the data for.
     *
     * @return A String representing the data.
     * @return null if an error occurs.
     */
    private String getRowSetItem( ResultSet rset, int col )
    {
       String buf = null;

       if( rset != null )
       {
          try
          {
             buf = rset.getString( col );
          }
          catch( Exception sqle )
          {
             sqle.printStackTrace();
          }
       }

       return ( buf );
    }
    
    /**
     * Create a 2D array of Strings from the JDBC result set.
     *
     *
     * @param rset
     * @return The 2 dimensional array representing the result set.
     */
    private String[][] create2DArray( ResultSet rset )
    {
       int               increment    = 10;
       int               currentRow   = 0;
       int               arraySize    = increment;
       int               numCols      = 0;
       ResultSetMetaData rsetMetaData = null;

       if( rset != null )
       {
          try
          {
             rsetMetaData = rset.getMetaData();
             numCols      = rsetMetaData.getColumnCount();
          }
          catch( Exception jsqle )
          {
             jsqle.printStackTrace();
          }
       }

       m_numCols = numCols;

       String[][] ret  = new String[ increment ][ numCols ];
       String[][] temp = new String[ 1 ][ 1 ];

       assignColNames( rsetMetaData );

       while( nextResSetRow( rset ) )
       {
          try
          {
             if( currentRow < arraySize )
             {
                // copy the current row into the return array
                for( int col = 0; col < numCols; col++ )
                {
                   ret[ currentRow ][ col ] = getRowSetItem( rset, col + 1 );
                }
             }
             else
             {
                // copy contents of ret to temp
                temp = new String[ currentRow + 1 ][ numCols + 1 ];

                System.arraycopy( ret, 0, temp, 0, currentRow );

                // We need to re-allocate the array here
                arraySize = currentRow + increment;
                ret       = new String[ arraySize ][ numCols + 1 ];

                System.arraycopy( temp, 0, ret, 0, currentRow );

                // copy the current row into the return array
                for( int col = 0; col < numCols; col++ )
                {
                   ret[ currentRow ][ col ] = getRowSetItem( rset, col + 1 );
                }
             }
          }
          catch( ArrayIndexOutOfBoundsException e )
          {
             // nothing on purpose
          }
          catch( Exception e )
          {
             e.printStackTrace();
          }

          currentRow++;
       }

       m_numRows = currentRow;

       return ( ret );
    }
    
    /**
     * Execute the query.
     *
     * @return true if the query is executed successfully.
     * @return false otherwise.
     */
    public boolean executeQuery(String m_query)
    {
       boolean ret = false;
       m_twoDArray = null;
       Connection conn = null;
       Statement  stmt = null;
       if( (m_query != null) && !m_query.equals( "" ) ){
          conn = getConnection();
          if( conn != null ){
             try{
                stmt = conn.createStatement();
                if( stmt != null ){
                   stmt.setMaxRows( m_maxRows );
                   if( m_query.toUpperCase().trim().indexOf( SELECT ) == 0 ){
                      ResultSet resultSet = stmt.executeQuery( m_query );
                      m_twoDArray = create2DArray( resultSet );
                      resultSet.close();
                   }else{
                      stmt.executeUpdate( m_query );
                   }
                   stmt.close();
                }
                conn.close();
                ret = true;
             }
             catch( Exception e ){
                e.printStackTrace();
                System.out.println(m_query);
                try{
                   if( stmt != null ){
                      stmt.close();
                   }
                   conn.close();
                }catch( Exception se ){}
                throw new RuntimeException( e.getMessage() );
             }
          }else{
        	  throw new RuntimeException( "Connection error!" );
          }
          try{
       	   conn.close();
          }catch( Exception ex ){}
       }
       return ( ret );
    }
    
    /**
     * Execute the query.
     *
     * @return true if the query is executed successfully.
     * @return false otherwise.
     */
    public boolean uploadBlob( String m_table, String str_id, String str_blob, byte [] bytes, int int_id )
    {
       boolean ret = false;
       m_twoDArray = null;
       Connection conn = null;
       Statement  stmt = null;
      conn = getConnection();
      if( conn != null ){
         try{
            stmt = conn.createStatement();
            if( stmt != null ){
               stmt.setMaxRows( m_maxRows );
               
               String m_query = 
		   			" UPDATE "+m_table+" " +
					" SET "+str_blob+" = empty_blob()     " +
					" WHERE "+str_id+" = "+int_id+"       " ;
               stmt.executeUpdate( m_query );
               stmt.close();
               		
       		   stmt = conn.createStatement();
               	if( stmt != null ){
               		stmt.setMaxRows( m_maxRows );
                    m_query = 
                    	" SELECT "+str_blob+"                 " +  
                        " FROM "+m_table+"                    " +
                        " WHERE "+str_id+" =  "+int_id+"      " +
                        " FOR UPDATE                          " ;
                    
                   	ResultSet rs = stmt.executeQuery( m_query );
                   	
                   	if(rs.next()){
                   		BLOB b = ((OracleResultSet)rs).getBLOB(str_blob);
	                   	OutputStream oop = b.getBinaryOutputStream();
	                    oop.write(bytes);
	                    oop.close();
                   	}
                	stmt.close();
               	}
            }
            conn.close();
            ret = true;
         }
         catch( Exception e ){
            e.printStackTrace();
            try{
               if( stmt != null ){
                  stmt.close();
               }
               conn.close();
            }catch( Exception se ){}
            throw new RuntimeException( e.getMessage() );
         }
      }else{
    	  throw new RuntimeException( "Connection error!" );
      }
      try{
   	   conn.close();
      }catch( Exception ex ){}
       return ( ret );
    }
    
    /**
     * Execute the query.
     *
     * @return true if the query is executed successfully.
     * @return false otherwise.
     */
    public boolean uploadBlob( String m_table, String str_id, String str_blob, byte [] bytes, String str_idValue )
    {
       boolean ret = false;
       m_twoDArray = null;
       Connection conn = null;
       Statement  stmt = null;
      conn = getConnection();
      if( conn != null ){
         try{
            stmt = conn.createStatement();
            if( stmt != null ){
               stmt.setMaxRows( m_maxRows );
               
               String m_query = 
		   			" UPDATE "+m_table+" " +
					" SET "+str_blob+" = empty_blob()     " +
					" WHERE "+str_id+" = '"+str_idValue+"'       " ;
               stmt.executeUpdate( m_query );
               stmt.close();
               		
       		   stmt = conn.createStatement();
               	if( stmt != null ){
               		stmt.setMaxRows( m_maxRows );
                    m_query = 
                    	" SELECT "+str_blob+"                 " +  
                        " FROM "+m_table+"                    " +
                        " WHERE "+str_id+" =  '"+str_idValue+"'      " +
                        " FOR UPDATE                          " ;
                    
                   	ResultSet rs = stmt.executeQuery( m_query );
                   	
                   	if(rs.next()){
                   		BLOB b = ((OracleResultSet)rs).getBLOB(str_blob);
	                   	OutputStream oop = b.getBinaryOutputStream();
	                    oop.write(bytes);
	                    oop.close();
                   	}
                	stmt.close();
               	}
            }
            conn.close();
            ret = true;
         }
         catch( Exception e ){
            e.printStackTrace();
            try{
               if( stmt != null ){
                  stmt.close();
               }
               conn.close();
            }catch( Exception se ){}
            throw new RuntimeException( e.getMessage() );
         }
      }else{
    	  throw new RuntimeException( "Connection error!" );
      }
      try{
   	   conn.close();
      }catch( Exception ex ){}
       return ( ret );
    }
    
    public String getFile(String m_table, String str_id, String str_blob, int int_id, String str_path, String str_file)throws Exception{
    	String str_retVal = "";
    	Connection conn = null;
    	Statement  stmt = null;
    	conn = getConnection();
    	if( conn != null ){
    		stmt = conn.createStatement();
    		if( stmt != null ){
           		stmt.setMaxRows( m_maxRows );
                String m_query = 
                	" SELECT "+str_blob+",                   " +
                	"              "+str_file+"                      " +
                    " FROM "+m_table+"                       " +
                    " WHERE "+str_id+" =  "+int_id+"  ";
                
               	ResultSet rs = stmt.executeQuery( m_query );
               	if(rs.next()){
               		str_retVal = rs.getString(str_file);
               		String pathname = str_path + "\\" + str_retVal;
               		File file = new File(pathname);
               		BufferedOutputStream fos =new BufferedOutputStream(new FileOutputStream(file));
               		BLOB b = (BLOB) rs.getBlob(str_blob);
               		BufferedInputStream inStream = new BufferedInputStream( b.getBinaryStream() );
               		int size = (int)b.length();
               		byte [] byt_buffer = new byte[size];
               		int length = -1;
               		while ((length = inStream.read(byt_buffer)) != -1) {
               			fos.write(byt_buffer, 0, length);
               		} 
               		inStream.close();
               		fos.close();
               	}
            	stmt.close();
           	}
    	}
    	return str_retVal;
    }
    
    public String getFile(String m_table, String str_id, String str_blob, String str_valueId, String str_path, String str_file)throws Exception{
    	String str_retVal = "";
    	Connection conn = null;
    	Statement  stmt = null;
    	conn = getConnection();
    	if( conn != null ){
    		stmt = conn.createStatement();
    		if( stmt != null ){
           		stmt.setMaxRows( m_maxRows );
                String m_query = 
                	" SELECT "+str_blob+",                   " +
                	"        "+str_file+"                      " +
                    " FROM "+m_table+"                       " +
                    " WHERE "+str_id+" =  '"+str_valueId+"'  ";
                
               	ResultSet rs = stmt.executeQuery( m_query );
               	if(rs.next()){
               		str_retVal = rs.getString(str_file);
               		String pathname = str_path + "\\" + str_retVal;
               		File file = new File(pathname);
               		BufferedOutputStream fos =new BufferedOutputStream(new FileOutputStream(file));
               		BLOB b = (BLOB) rs.getBlob(str_blob);
               		BufferedInputStream inStream = new BufferedInputStream( b.getBinaryStream() );
               		int size = (int)b.length();
               		byte [] byt_buffer = new byte[size];
               		int length = -1;
               		if( size > 0 ){
	               		while ((length = inStream.read(byt_buffer)) != -1) {
	               			fos.write(byt_buffer, 0, length);
	               		} 
               		}
               		inStream.close();
               		fos.close();
               	}
            	stmt.close();
           	}
    	}
    	return str_retVal;
    }
    
    public String getItem( int row, int col ){
       String ret = null;
       if( !( (row > getNumRows()) || (col > getNumCols()) ) ){
          ret = m_twoDArray[ row ][ col ];
       }
       return ( ret );
    }
    
    public String getColumnName( int col ){
    	return this.m_colNames[col];
    }
}